/**
 * @author Mie Plougstrup, Anders Abildgaard, Bo Stokholm 
 * @Version 16-12-2014
 */
package dbLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import modelLayer.ChartecCode;

public class DbChartecCode {
	private Connection con;

	/**
	 * Constructor
	 */
	public DbChartecCode() {
		con = DbConnection.getInstance().getDBcon();
	}

	/**
	 * Method for retrieving an chartecCode object from the database.
	 * 
	 * @param chartecCode
	 * @return
	 */
	public ChartecCode retrieveByCode(int chartecCode) {
		String wClause = "chartecCode = " + chartecCode;
		return singleWhere(wClause);
	}
	
	/**
	 * Method for retrieving all chartecCodes.
	 * 
	 * @return		An ArrayList of ChartecCode objects.
	 */
	public ArrayList<ChartecCode> retrieveAll() {
		String wClause = "";
		return multiWhere(wClause);
	}

	/**
	 * Method for inserting data into chartecCode table in database.
	 * 
	 * @param ccObj			The ChartecCode object.
	 * @return				Number of rows affected in database.
	 * @throws Exception
	 */
	public int insert(ChartecCode ccObj) throws Exception {
		int rc = -1; //Rows affected in database
		PreparedStatement prpStmt = null;
		String query = "INSERT INTO chartecCode(chartecCode, shippingPlace, hasDeliveryNo) " +
				"VALUES (?,?,?)";
		try {
			prpStmt = con.prepareStatement(query);
			prpStmt.setInt(1, ccObj.getChartecCode());
			prpStmt.setString(2, ccObj.getShippingPlace());
			if(ccObj.hasDeliveryNo()) {
				prpStmt.setByte(3, (byte) 1);
			} else {
				prpStmt.setByte(3, (byte) 0);
			}
			prpStmt.setQueryTimeout(5);
			rc = prpStmt.executeUpdate();
			prpStmt.close();
		} catch(Exception e) {
			throw new Exception("Insert into chartecCode exception: " + e.getMessage());
		}
		return rc;
	}

	/**
	 * Method for updating data about a chartecCode in the database.
	 * 
	 * @param ccObj			The ChartecCode object.
	 * @return				Number of rows affected in database.
	 * @throws Exception
	 */
	public int update(ChartecCode ccObj) throws Exception {
		int rc = -1; //Rows affected in database
		byte hasDeliveryNo = 0;
		if(ccObj.hasDeliveryNo()) {
			hasDeliveryNo = 1;
		}

		String query = "UPDATE chartecCode SET " +
				"shippingPlace = '"	+ ccObj.getShippingPlace() +
				"', hasDeliveryNo = '" + hasDeliveryNo +
				"' WHERE chartecCode = " + ccObj.getChartecCode();

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {
			throw new Exception("Update chartecCode exception: " + e.getMessage());
		}
		return rc;
	}
	
	/**
	 * Method for deleting a chartecCode in the database.
	 * 
	 * @param ccObj			The ChartecCode object.
	 * @return				Number of rows affected in database.
	 * @throws Exception
	 */
	public int delete(ChartecCode ccObj) throws Exception {
		int rc = -1; //Rows affected in database

		String query = "DELETE FROM chartecCode WHERE chartecCode = " + ccObj.getChartecCode();

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {
			throw new Exception("Delete chartecCode exception: " + e.getMessage());
		}
		return rc;
	}
	
	/**
	 * Method that returns a list of chartecCode objects.
	 * MultiWhere is used when we select more than one chartecCode from the database.
	 * 
	 * @param wClause				The where-clause in the select query.
	 * @return						ArrayList containing ChartecCode objects.
	 */
	private ArrayList<ChartecCode> multiWhere(String wClause) {
		ResultSet results;
		ArrayList<ChartecCode> list = new ArrayList<ChartecCode>();
		String query = buildSelectQuery(wClause);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				ChartecCode ccObj = buildChartecCode(results);
				list.add(ccObj);
			}
			stmt.close();
		} catch(Exception e) {
		}
		return list;
	}

	/**
	 * Method that returns an chartecCode object.
	 * SingleWhere is used when we only select one chartecCode from the database.
	 * 
	 * @param wClause				The where-clause in the select query.
	 * @return						The ChartecCode object.
	 */
	private ChartecCode singleWhere(String wClause) {
		ResultSet results;
		ChartecCode ccObj = new ChartecCode();
		String query = buildSelectQuery(wClause);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				ccObj = buildChartecCode(results);
				stmt.close();
			} else {
				ccObj = null;
			}
		} catch(Exception e) {
		}
		return ccObj;
	}

	/**
	 * Method that builds the SQL select query.
	 * 
	 * @param wClause	The where-clause in the query.
	 * @return 			The query string.
	 */
	private String buildSelectQuery(String wClause) {
		String query = "SELECT chartecCode, shippingPlace, hasDeliveryNo FROM ChartecCode";

		if (wClause.length() > 0) {
			query += " WHERE " + wClause;
		}
		return query;
	}

	/**
	 * Method that builds the chartecCode object from data retrieved in the datebase.
	 * 
	 * @param results	The resultSet returned from the database.
	 * @return			The chartecCode object.
	 */
	private ChartecCode buildChartecCode(ResultSet results) {
		ChartecCode ccObj = new ChartecCode();
		
		try {
			ccObj.setChartecCode(results.getInt("chartecCode"));
			ccObj.setShippingPlace(results.getString("shippingPlace"));
			ccObj.setHasDeliveryNo(results.getBoolean("hasDeliveryNo"));
		} catch (Exception e) {
		}
		return ccObj;
	}
	
}
